 1.电商分析之--会员活跃度之DWD层建表和数据加载
   
   2020-08-02 18:19:32.966 [main] INFO
com.lagou.ecommerce.AppStart - {"app_active":
{"name":"app_active","json":
{"entry":"1","action":"1","error_code":"0"},"time":159630958586
1},"attr":{"area":"绍兴","uid":"2F10092A10","app_v":"1.1.16"
,"event_type":"common","device_id":"1FB872-9A10010","os_type":"3.0",
"channel":"ML","language":"chinese","brand":"Huawei-2"}}
2020-08-02
   
   主要任务：ODS（包含json串） => DWD
   json数据解析，丢弃无用数据（数据清洗），保留有效信息，并将数据展开，形成每
日启动明细表。
   1).创建DWD层表
   use DWD;
   drop table if exists dwd.dwd_start_log;
   CREATE TABLE dwd.dwd_start_log(
`device_id` string,
`area` string,
`uid` string,
`app_v` string,
`event_type` string,
`os_type` string,
`channel` string,
`language` string,
`brand` string,
`entry` string,
`action` string,
`error_code` string
)
PARTITIONED BY (dt string)
STORED AS parquet;
   表的格式：parquet、分区表
   2).加载DWD层数据
with tmp as(   
select split(str, ' ')[7] line
from ods.ods_start_log
where dt='2020-07-30' limit 10
)
select get_json_object(line, '$.attr.device_id'),
get_json_object(line, '$.attr.area'),
get_json_object(line, '$.attr.uid'),
get_json_object(line, '$.attr.app_v'),
get_json_object(line, '$.attr.event_type'),
get_json_object(line, '$.attr.os_type'),
get_json_object(line, '$.attr.channel'),
get_json_object(line, '$.attr.language'),
get_json_object(line, '$.attr.brand'),
get_json_object(line, '$.app_active.json.entry'),
get_json_object(line, '$.app_active.json.action'),
get_json_object(line, '$.app_active.json.error_code')
from tmp;
   script/member_active/dwd_load_start.sh
   
#！/bin/bash

source /etc/profile

# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

# 定义要执行的SQL
sql="
with tmp as(
select split(str, ' ')[7] line
from ods.ods_start_log
where dt='$do_date'
)
insert overwrite table dwd.dwd_start_log
partition(dt='$do_date')
select get_json_object(line, '$.attr.device_id'),
get_json_object(line, '$.attr.area'),
get_json_object(line, '$.attr.uid'),
get_json_object(line, '$.attr.app_v'),
get_json_object(line, '$.attr.event_type'),
get_json_object(line, '$.attr.os_type'),
get_json_object(line, '$.attr.channel'),
get_json_object(line, '$.attr.language'),
get_json_object(line, '$.attr.brand'),
get_json_object(line, '$.app_active.json.entry'),
get_json_object(line, '$.app_active.json.action'),
get_json_object(line, '$.app_active.json.error_code')
from tmp;"

hive -e "$sql"
   日志文件 =》 Flume =》 HDFS =》 ODS =》 DWD
   ODS =》 DWD；json数据的解析；数据清洗
   
   下一步任务：DWD（会员的每日启动信息明细） => DWS(如何建表，如何加载数据)
   活跃会员 ===> 新增会员 ===> 会员留存
 
 2.活跃会员
   
   活跃会员：打开应用的会员即为活跃会员；
   新增会员：第一次使用应用的用户，定义为新增会员；
   留存会员：某段时间的新增会员，经过一段时间后，仍继续使用应用认为是留存会员；
   
   活跃会员指标需求：每日、每周、每月的活跃会员数
   DWD：会员的每日启动信息明细（会员都是活跃会员；某个会员可能会出现多次）
   DWS：每日活跃会员信息(关键)、每周活跃会员信息、每月活跃会员信息
   每日活跃会员信息 ===> 每周活跃会员信息
   每日活跃会员信息 ===> 每月活跃会员信息
   ADS：每日、每周、每月活跃会员数(输出)
   
   ADS表结构：
   daycnt weekcnt monthcnt dt
   备注：周、月为自然周、自然月
   
   处理过程：
   (1)、建表(每日、每周、每月活跃会员信息)
   (2)、每日启动明细 ===> 每日活跃会员
   (3)、每日活跃会员 => 每周活跃会员；每日活跃会员 => 每月活跃会员
   (4)、汇总生成ADS层的数据
   
   1).创建DWS层表
   use dws;
   drop table if exists dws.dws_member_start_day;
   create table dws.dws_member_start_day
(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string
) COMMENT '会员日启动汇总'
partitioned by(dt string)
stored as parquet;
   
   drop table if exists dws.dws_member_start_week;
   create table dws.dws_member_start_week(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`week` string
) COMMENT '会员周启动汇总'
PARTITIONED BY (`dt` string)
stored as parquet;
    
   drop table if exists dws.dws_member_start_month;
   create table dws.dws_member_start_month(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`month` string
) COMMENT '会员月启动汇总'
PARTITIONED BY (`dt` string)
stored as parquet;
   2).加载DWS层数据
   script/member_active/dws_load_member_start.sh

#！/bin/bash

source /etc/profile

# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

# 定义要执行的SQL
# 汇总得到每日活跃会员信息；每日数据汇总得到每周、每月数据
sql="
insert overwrite table dws.dws_member_start_day
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand))
from dwd.dwd_start_log
where dt='$do_date'
group by device_id;

-- 汇总得到每周活跃会员
insert overwrite table dws.dws_member_start_week
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand)),
date_add(next_day('$do_date', 'mo'), -7)
from dws.dws_member_start_day
where dt >= date_add(next_day('$do_date', 'mo'), -7)
and dt <= '$do_date'
group by device_id;

-- 汇总得到每月活跃会员
insert overwrite table dws.dws_member_start_month
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand)),
date_format('$do_date', 'yyyy-MM')
from dws.dws_member_start_day
where dt >= date_format('$do_date', 'yyyy-MM-01')
and dt <= '$do_date'
group by device_id;
"

hive -e "$sql"
   注意shell的引号
   ODS => DWD => DWS(每日、每周、每月活跃会员的汇总表)
   3).创建ADS层表
   计算当天、当周、当月活跃会员数量
   use ads;
   drop table if exists ads.ads_member_active_count;
   create table ads.ads_member_active_count(
`day_count` int COMMENT '当日会员数量',
`week_count` int COMMENT '当周会员数量',
`month_count` int COMMENT '当月会员数量'
) COMMENT '活跃会员数'
partitioned by(dt string)
row format delimited fields terminated by ',';
   4).加载ADS层数据
   script/member_active/ads_load_member_active.sh

#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
with tmp as(
select 'day' datelabel, count(*) cnt, dt
from dws.dws_member_start_day
where dt='$do_date'
group by dt
union all
select 'week' datelabel, count(*) cnt, dt
from dws.dws_member_start_week
where dt='$do_date'
group by dt
union all
select 'month' datelabel, count(*) cnt, dt
from dws.dws_member_start_month
where dt='$do_date'
group by dt
)
insert overwrite table ads.ads_member_active_count
partition(dt='$do_date')
select sum(case when datelabel='day' then cnt end) as
day_count,
sum(case when datelabel='week' then cnt end) as
week_count,
sum(case when datelabel='month' then cnt end) as
month_count
from tmp
group by dt;
"
    
hive -e "$sql"
   
   script/member_active/ads_load_member_active1.sh
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ads.ads_member_active_count
partition(dt='$do_date')
select daycnt, weekcnt, monthcnt
from (select dt, count(*) daycnt
from dws.dws_member_start_day
where dt='$do_date'
group by dt
) day join
(select dt, count(*) weekcnt
from dws.dws_member_start_week
where dt='$do_date'
group by dt
) week on day.dt=week.dt
join
(select dt, count(*) monthcnt
from dws.dws_member_start_month
where dt='$do_date'
group by dt
) month on day.dt=month.dt;
"

hive -e "$sql"
   5).小结
   脚本执行次序：
   ods_load_start.sh
   dwd_load_start.sh
   dws_load_member_start.sh
   ads_load_member_active.sh
   ads_load_member_active1.sh